"""
数据源字段服务
"""
import json
import pandas as pd
from typing import List, Dict, Any, Optional
from sqlalchemy.orm import Session
from sqlalchemy import text

from models.data_source import DataSource, DataSourceType
from models.data_source_field import DataSourceField, FIELD_TYPES
from services.data_source_service import DataSourceService
from schemas.data_source import MySQLConfig, CSVConfig, ExcelConfig
import pymysql
from schemas.data_source_field import (
    DataSourceFieldCreate, 
    DataSourceFieldUpdate,
    DataSourceFieldAnalysis,
    DataSourceDataPreview,
    DataSourceQuery
)


class DataSourceFieldService:
    """数据源字段服务"""

    @staticmethod
    def get_table_fields(db: Session, data_source_id: int, table_name: str, user_id: int, identify_types: bool = False):
        """实时获取指定表的字段列表"""
        # 获取数据源
        data_source = DataSourceService.get_data_source(db, data_source_id, user_id)
        if not data_source:
            raise ValueError("数据源不存在")

        try:
            config = json.loads(data_source.config) if isinstance(data_source.config, str) else data_source.config

            if data_source.type == DataSourceType.MYSQL:
                fields = DataSourceFieldService._get_mysql_table_fields(config, table_name)
            elif data_source.type == DataSourceType.CSV:
                fields = DataSourceFieldService._get_csv_fields(config)
            elif data_source.type == DataSourceType.EXCEL:
                fields = DataSourceFieldService._get_excel_fields(config, table_name)
            else:
                raise ValueError("不支持的数据源类型")

            # 如果需要识别字段类型
            if identify_types:
                fields = DataSourceFieldService._identify_field_types(fields)

            return fields

        except Exception as e:
            print(f"获取表字段异常: {str(e)}")
            import traceback
            traceback.print_exc()
            raise ValueError(f"获取表字段失败: {str(e)}")

    @staticmethod
    def analyze_data_source_fields(db: Session, data_source_id: int, user_id: int, table_name: str = None) -> DataSourceFieldAnalysis:
        """分析数据源字段"""
        # 获取数据源
        data_source = db.query(DataSource).filter(
            DataSource.id == data_source_id,
            DataSource.user_id == user_id
        ).first()
        
        if not data_source:
            raise ValueError("数据源不存在")

        fields = []
        sample_data = None

        try:
            if data_source.type == DataSourceType.MYSQL:
                fields, sample_data = DataSourceFieldService._analyze_mysql_fields(data_source, table_name)
            elif data_source.type == DataSourceType.CSV:
                fields, sample_data = DataSourceFieldService._analyze_csv_fields(data_source)
            elif data_source.type == DataSourceType.EXCEL:
                fields, sample_data = DataSourceFieldService._analyze_excel_fields(data_source)
        except Exception as e:
            print(f"字段分析异常: {str(e)}")
            import traceback
            traceback.print_exc()
            raise ValueError(f"分析数据源字段失败: {str(e)}")

        try:
            # 保存字段信息到数据库
            DataSourceFieldService._save_fields_to_db(db, data_source_id, fields)
        except Exception as e:
            print(f"保存字段异常: {str(e)}")
            import traceback
            traceback.print_exc()
            raise ValueError(f"保存字段信息失败: {str(e)}")

        # 分类字段
        email_fields = [f.name for f in fields if f.is_email]
        name_fields = [f.name for f in fields if f.is_name]
        primary_fields = [f.name for f in fields if f.is_primary]

        return DataSourceFieldAnalysis(
            fields=fields,
            total_fields=len(fields),
            email_fields=email_fields,
            name_fields=name_fields,
            primary_fields=primary_fields,
            sample_data=sample_data
        )

    @staticmethod
    def _get_mysql_table_fields(config: Dict[str, Any], table_name: str):
        """获取MySQL表的字段列表"""
        mysql_config = MySQLConfig(**config)
        connection = pymysql.connect(
            host=mysql_config.host,
            port=mysql_config.port,
            user=mysql_config.username,
            password=mysql_config.password,
            database=mysql_config.database,
            charset=mysql_config.charset
        )

        try:
            with connection.cursor() as cursor:
                # 获取表字段信息
                cursor.execute("""
                    SELECT
                        COLUMN_NAME,
                        DATA_TYPE,
                        IS_NULLABLE,
                        COLUMN_KEY,
                        COLUMN_DEFAULT,
                        COLUMN_COMMENT,
                        CHARACTER_MAXIMUM_LENGTH,
                        NUMERIC_PRECISION,
                        NUMERIC_SCALE
                    FROM
                        INFORMATION_SCHEMA.COLUMNS
                    WHERE
                        TABLE_SCHEMA = %s
                        AND TABLE_NAME = %s
                    ORDER BY ORDINAL_POSITION
                """, (mysql_config.database, table_name))

                fields = []
                for row in cursor.fetchall():
                    field_type = DataSourceFieldService._map_mysql_type_to_field_type(row[1])
                    fields.append({
                        "name": row[0],
                        "display_name": row[0],
                        "field_type": field_type,
                        "is_nullable": row[2] == "YES",
                        "is_primary": row[3] == "PRI",
                        "default_value": row[4],
                        "description": row[5] or "",
                        "max_length": row[6],
                        "precision": row[7],
                        "scale": row[8],
                        "is_email": False,
                        "is_name": False,
                    })

                return fields
        finally:
            connection.close()

    @staticmethod
    def _map_mysql_type_to_field_type(mysql_type: str) -> str:
        """映射MySQL数据类型到字段类型"""
        mysql_type = mysql_type.lower()
        if mysql_type in ['varchar', 'char', 'text', 'longtext', 'mediumtext', 'tinytext']:
            return 'string'
        elif mysql_type in ['int', 'bigint', 'smallint', 'tinyint', 'mediumint', 'decimal', 'float', 'double']:
            return 'number'
        elif mysql_type in ['date', 'datetime', 'timestamp', 'time', 'year']:
            return 'date'
        elif mysql_type in ['boolean', 'bool', 'bit']:
            return 'boolean'
        else:
            return 'string'

    @staticmethod
    def _get_csv_fields(config: Dict[str, Any]):
        """获取CSV文件的字段列表"""
        csv_config = CSVConfig(**config)

        try:
            df = pd.read_csv(
                csv_config.file_path,
                encoding=csv_config.encoding,
                delimiter=csv_config.delimiter,
                header=0 if csv_config.has_header else None,
                nrows=1  # 只读取第一行来获取列名
            )

            fields = []
            for col in df.columns:
                # 尝试推断数据类型
                sample_data = pd.read_csv(
                    csv_config.file_path,
                    encoding=csv_config.encoding,
                    delimiter=csv_config.delimiter,
                    header=0 if csv_config.has_header else None,
                    nrows=100  # 读取前100行来推断类型
                )[col]

                field_type = DataSourceFieldService._infer_field_type_from_series(sample_data)

                fields.append({
                    "name": str(col),
                    "display_name": str(col),
                    "field_type": field_type,
                    "is_nullable": True,
                    "is_primary": False,
                    "default_value": None,
                    "description": "",
                    "max_length": None,
                    "precision": None,
                    "scale": None,
                    "is_email": False,
                    "is_name": False,
                })

            return fields
        except Exception as e:
            raise ValueError(f"读取CSV文件失败: {str(e)}")

    @staticmethod
    def _get_excel_fields(config: Dict[str, Any], sheet_name: str = None):
        """获取Excel文件的字段列表"""
        excel_config = ExcelConfig(**config)
        target_sheet = sheet_name or excel_config.sheet_name

        try:
            df = pd.read_excel(
                excel_config.file_path,
                sheet_name=target_sheet,
                header=0 if excel_config.has_header else None,
                skiprows=excel_config.start_row,
                nrows=1  # 只读取第一行来获取列名
            )

            fields = []
            for col in df.columns:
                # 尝试推断数据类型
                sample_data = pd.read_excel(
                    excel_config.file_path,
                    sheet_name=target_sheet,
                    header=0 if excel_config.has_header else None,
                    skiprows=excel_config.start_row,
                    nrows=100  # 读取前100行来推断类型
                )[col]

                field_type = DataSourceFieldService._infer_field_type_from_series(sample_data)

                fields.append({
                    "name": str(col),
                    "display_name": str(col),
                    "field_type": field_type,
                    "is_nullable": True,
                    "is_primary": False,
                    "default_value": None,
                    "description": "",
                    "max_length": None,
                    "precision": None,
                    "scale": None,
                    "is_email": False,
                    "is_name": False,
                })

            return fields
        except Exception as e:
            raise ValueError(f"读取Excel文件失败: {str(e)}")

    @staticmethod
    def _infer_field_type_from_series(series):
        """从pandas Series推断字段类型"""
        # 去除空值
        non_null_series = series.dropna()
        if len(non_null_series) == 0:
            return 'string'

        # 检查是否为数字类型
        if pd.api.types.is_numeric_dtype(non_null_series):
            return 'number'

        # 检查是否为日期类型
        if pd.api.types.is_datetime64_any_dtype(non_null_series):
            return 'date'

        # 检查是否为布尔类型
        if pd.api.types.is_bool_dtype(non_null_series):
            return 'boolean'

        # 默认为字符串类型
        return 'string'

    @staticmethod
    def _identify_field_types(fields):
        """智能识别字段类型（邮箱、姓名等）"""
        for field in fields:
            field_name = field['name'].lower()

            # 识别邮箱字段
            if any(keyword in field_name for keyword in ['email', 'mail', '邮箱', '邮件']):
                field['is_email'] = True

            # 识别姓名字段
            if any(keyword in field_name for keyword in ['name', 'username', 'user_name', '姓名', '用户名', '名字', '名称']):
                field['is_name'] = True

        return fields

    @staticmethod
    def _analyze_mysql_fields(data_source: DataSource, table_name: str = None) -> tuple:
        """分析MySQL数据源字段"""
        import pymysql
        
        config = json.loads(data_source.config) if isinstance(data_source.config, str) else data_source.config
        connection = pymysql.connect(
            host=config["host"],
            port=config["port"],
            user=config["username"],
            password=config["password"],
            database=config["database"],
            charset='utf8mb4'
        )
        
        try:
            with connection.cursor() as cursor:
                # 获取表结构
                if not table_name:
                    # 如果没有指定表名，从配置中获取或使用第一个表
                    table_name = config.get("table_name", "")
                    if not table_name:
                        # 获取第一个表
                        cursor.execute("SHOW TABLES")
                        tables = cursor.fetchall()
                        if tables:
                            table_name = tables[0][0]
                        else:
                            raise ValueError("数据库中没有表")

                # 获取字段信息
                cursor.execute(f"DESCRIBE `{table_name}`")
                columns = cursor.fetchall()
                
                fields = []
                for col in columns:
                    field_name = col[0]
                    field_type = DataSourceFieldService._mysql_type_to_standard(col[1])
                    is_primary = col[3] == "PRI"
                    is_nullable = col[2] == "YES"
                    
                    # 智能识别邮箱和姓名字段
                    is_email = "email" in field_name.lower() or "mail" in field_name.lower()
                    is_name = "name" in field_name.lower() or field_name.lower() in ["姓名", "用户名", "username"]
                    
                    field = DataSourceFieldCreate(
                        name=field_name,
                        display_name=field_name,
                        field_type=field_type,
                        is_primary=is_primary,
                        is_nullable=is_nullable,
                        is_email=is_email,
                        is_name=is_name
                    )
                    fields.append(field)

                # 获取样例数据
                cursor.execute(f"SELECT * FROM `{table_name}` LIMIT 3")
                sample_rows = cursor.fetchall()
                
                # 获取列名
                cursor.execute(f"SHOW COLUMNS FROM `{table_name}`")
                column_info = cursor.fetchall()
                column_names = [col[0] for col in column_info]
                
                sample_data = []
                for row in sample_rows:
                    sample_data.append(dict(zip(column_names, row)))

                return fields, sample_data

        finally:
            connection.close()

    @staticmethod
    def _analyze_csv_fields(data_source: DataSource) -> tuple:
        """分析CSV数据源字段"""
        config = json.loads(data_source.config) if isinstance(data_source.config, str) else data_source.config
        file_path = config["file_path"]
        
        # 读取CSV文件
        df = pd.read_csv(file_path, nrows=100)  # 只读取前100行用于分析
        
        fields = []
        for col_name in df.columns:
            # 推断字段类型
            field_type = DataSourceFieldService._pandas_dtype_to_standard(df[col_name].dtype)
            
            # 智能识别邮箱和姓名字段
            is_email = "email" in col_name.lower() or "mail" in col_name.lower()
            is_name = "name" in col_name.lower() or col_name.lower() in ["姓名", "用户名", "username"]
            
            # 获取样例值
            sample_value = str(df[col_name].iloc[0]) if len(df) > 0 else None
            
            field = DataSourceFieldCreate(
                name=col_name,
                display_name=col_name,
                field_type=field_type,
                is_email=is_email,
                is_name=is_name,
                sample_value=sample_value
            )
            fields.append(field)

        # 获取样例数据
        sample_data = df.head(3).to_dict('records')
        
        return fields, sample_data

    @staticmethod
    def _analyze_excel_fields(data_source: DataSource) -> tuple:
        """分析Excel数据源字段"""
        config = json.loads(data_source.config) if isinstance(data_source.config, str) else data_source.config
        file_path = config["file_path"]
        sheet_name = config.get("sheet_name", 0)
        
        # 读取Excel文件
        df = pd.read_excel(file_path, sheet_name=sheet_name, nrows=100)
        
        fields = []
        for col_name in df.columns:
            # 推断字段类型
            field_type = DataSourceFieldService._pandas_dtype_to_standard(df[col_name].dtype)
            
            # 智能识别邮箱和姓名字段
            is_email = "email" in str(col_name).lower() or "mail" in str(col_name).lower()
            is_name = "name" in str(col_name).lower() or str(col_name).lower() in ["姓名", "用户名", "username"]
            
            # 获取样例值
            sample_value = str(df[col_name].iloc[0]) if len(df) > 0 else None
            
            field = DataSourceFieldCreate(
                name=str(col_name),
                display_name=str(col_name),
                field_type=field_type,
                is_email=is_email,
                is_name=is_name,
                sample_value=sample_value
            )
            fields.append(field)

        # 获取样例数据
        sample_data = df.head(3).to_dict('records')
        
        return fields, sample_data

    @staticmethod
    def _mysql_type_to_standard(mysql_type: str) -> str:
        """MySQL类型转换为标准类型"""
        mysql_type = mysql_type.lower()
        if "varchar" in mysql_type or "char" in mysql_type or "text" in mysql_type:
            return "text"
        elif "int" in mysql_type or "decimal" in mysql_type or "float" in mysql_type or "double" in mysql_type:
            return "number"
        elif "date" in mysql_type:
            return "date" if mysql_type == "date" else "datetime"
        elif "json" in mysql_type:
            return "json"
        else:
            return "text"

    @staticmethod
    def _pandas_dtype_to_standard(dtype) -> str:
        """Pandas数据类型转换为标准类型"""
        dtype_str = str(dtype)
        if "int" in dtype_str or "float" in dtype_str:
            return "number"
        elif "datetime" in dtype_str:
            return "datetime"
        elif "bool" in dtype_str:
            return "boolean"
        else:
            return "text"

    @staticmethod
    def _save_fields_to_db(db: Session, data_source_id: int, fields: List[DataSourceFieldCreate]):
        """保存字段信息到数据库"""
        # 删除旧的字段信息
        db.query(DataSourceField).filter(DataSourceField.data_source_id == data_source_id).delete()
        
        # 保存新的字段信息
        for field in fields:
            db_field = DataSourceField(
                data_source_id=data_source_id,
                **field.dict()
            )
            db.add(db_field)
        
        db.commit()

    @staticmethod
    def get_data_source_fields(db: Session, data_source_id: int, user_id: int) -> List[DataSourceField]:
        """获取数据源字段列表"""
        # 验证数据源权限
        data_source = db.query(DataSource).filter(
            DataSource.id == data_source_id,
            DataSource.user_id == user_id
        ).first()
        
        if not data_source:
            raise ValueError("数据源不存在")

        return db.query(DataSourceField).filter(
            DataSourceField.data_source_id == data_source_id
        ).all()

    @staticmethod
    def update_field(db: Session, field_id: int, field_update: DataSourceFieldUpdate, user_id: int) -> DataSourceField:
        """更新字段信息"""
        # 获取字段并验证权限
        field = db.query(DataSourceField).join(DataSource).filter(
            DataSourceField.id == field_id,
            DataSource.user_id == user_id
        ).first()
        
        if not field:
            raise ValueError("字段不存在")

        # 更新字段
        for key, value in field_update.dict(exclude_unset=True).items():
            setattr(field, key, value)
        
        db.commit()
        db.refresh(field)
        return field

    @staticmethod
    def query_data_source_data(db: Session, query: DataSourceQuery, user_id: int) -> DataSourceDataPreview:
        """查询数据源数据"""
        # 获取数据源
        data_source = db.query(DataSource).filter(
            DataSource.id == query.data_source_id,
            DataSource.user_id == user_id
        ).first()
        
        if not data_source:
            raise ValueError("数据源不存在")

        # 获取字段信息
        fields = DataSourceFieldService.get_data_source_fields(db, query.data_source_id, user_id)
        
        # 查询数据
        data = []
        total_rows = 0
        
        try:
            if data_source.type == "mysql":
                data, total_rows = DataSourceFieldService._query_mysql_data(data_source, query)
            elif data_source.type == "csv":
                data, total_rows = DataSourceFieldService._query_csv_data(data_source, query)
            elif data_source.type == "excel":
                data, total_rows = DataSourceFieldService._query_excel_data(data_source, query)
        except Exception as e:
            raise ValueError(f"查询数据失败: {str(e)}")

        return DataSourceDataPreview(
            fields=fields,
            data=data,
            total_rows=total_rows,
            sample_size=len(data)
        )

    @staticmethod
    def _query_mysql_data(data_source: DataSource, query: DataSourceQuery) -> tuple:
        """查询MySQL数据"""
        import pymysql
        
        config = data_source.config
        connection = pymysql.connect(
            host=config["host"],
            port=config["port"],
            user=config["username"],
            password=config["password"],
            database=config["database"],
            charset='utf8mb4'
        )
        
        try:
            with connection.cursor() as cursor:
                table_name = query.table_name or config.get("table_name", "")
                if not table_name:
                    cursor.execute("SHOW TABLES")
                    tables = cursor.fetchall()
                    if tables:
                        table_name = tables[0][0]
                    else:
                        raise ValueError("数据库中没有表")

                # 构建查询SQL
                fields_str = "*"
                if query.fields:
                    fields_str = ", ".join([f"`{field}`" for field in query.fields])

                sql = f"SELECT {fields_str} FROM `{table_name}`"
                
                # 添加过滤条件
                if query.filters:
                    conditions = []
                    for field, value in query.filters.items():
                        conditions.append(f"`{field}` = %s")
                    if conditions:
                        sql += " WHERE " + " AND ".join(conditions)

                # 添加排序
                if query.order_by:
                    sql += f" ORDER BY `{query.order_by}`"

                # 添加分页
                sql += f" LIMIT {query.limit} OFFSET {query.offset}"

                # 执行查询
                if query.filters:
                    cursor.execute(sql, list(query.filters.values()))
                else:
                    cursor.execute(sql)
                
                rows = cursor.fetchall()
                
                # 获取列名
                cursor.execute(f"SHOW COLUMNS FROM `{table_name}`")
                column_info = cursor.fetchall()
                column_names = [col[0] for col in column_info]
                
                # 如果指定了字段，使用指定的字段名
                if query.fields:
                    column_names = query.fields

                data = []
                for row in rows:
                    data.append(dict(zip(column_names, row)))

                # 获取总行数
                count_sql = f"SELECT COUNT(*) FROM `{table_name}`"
                if query.filters:
                    conditions = []
                    for field, value in query.filters.items():
                        conditions.append(f"`{field}` = %s")
                    if conditions:
                        count_sql += " WHERE " + " AND ".join(conditions)
                    cursor.execute(count_sql, list(query.filters.values()))
                else:
                    cursor.execute(count_sql)
                
                total_rows = cursor.fetchone()[0]

                return data, total_rows

        finally:
            connection.close()

    @staticmethod
    def _query_csv_data(data_source: DataSource, query: DataSourceQuery) -> tuple:
        """查询CSV数据"""
        config = data_source.config
        file_path = config["file_path"]
        
        # 读取CSV文件
        df = pd.read_csv(file_path)
        
        # 应用过滤条件
        if query.filters:
            for field, value in query.filters.items():
                if field in df.columns:
                    df = df[df[field] == value]

        # 选择字段
        if query.fields:
            available_fields = [f for f in query.fields if f in df.columns]
            if available_fields:
                df = df[available_fields]

        # 排序
        if query.order_by and query.order_by in df.columns:
            df = df.sort_values(by=query.order_by)

        total_rows = len(df)
        
        # 分页
        start = query.offset
        end = start + query.limit
        df_page = df.iloc[start:end]
        
        data = df_page.to_dict('records')
        
        return data, total_rows

    @staticmethod
    def _query_excel_data(data_source: DataSource, query: DataSourceQuery) -> tuple:
        """查询Excel数据"""
        config = data_source.config
        file_path = config["file_path"]
        sheet_name = config.get("sheet_name", 0)
        
        # 读取Excel文件
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        
        # 应用过滤条件
        if query.filters:
            for field, value in query.filters.items():
                if field in df.columns:
                    df = df[df[field] == value]

        # 选择字段
        if query.fields:
            available_fields = [f for f in query.fields if f in df.columns]
            if available_fields:
                df = df[available_fields]

        # 排序
        if query.order_by and query.order_by in df.columns:
            df = df.sort_values(by=query.order_by)

        total_rows = len(df)
        
        # 分页
        start = query.offset
        end = start + query.limit
        df_page = df.iloc[start:end]
        
        data = df_page.to_dict('records')
        
        return data, total_rows
